package cts.com.connection;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DbConnection {
	public static void main(String[] args) throws SQLException,
			ClassNotFoundException {

		String createTableACInfo = "CREATE TABLE BENEFICIARY_ACCOUNT"
				+ " (Primary_Account_Id VARCHAR(50) References AccountInformation(Account_Id),"
				+ "Beneficiary_Ac_No VARCHAR(50),"
				+ "Beneficiary_Name VARCHAR(50),"
				+ "Beneficiary_IFSC VARCHAR(50),"
				+ "Beneficiary_Email VARCHAR(50),"
				+ "Beneficiary_Ph_No VARCHAR(10),"
				+ "Beneficiary_Bank VARCHAR(50),"
				+ " primary key(Primary_Account_Id,Beneficiary_Ac_No))";
		// createTableACInfo="DROP TABLE BENEFICIARY_ACCOUNT";
		String[] queries = { createTableACInfo };
		if (isTableCreated(queries)) {
			System.out.println("Table Create Successfully...");
		} else {
			System.err.println("Can't create table");
		}
	}

	/**
	 * Utility method to create table from query.
	 * 
	 * @return TODO
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean isTableCreated(String[] queries)
			throws ClassNotFoundException, SQLException {
		Connection connection = new DbConnection().getConnection();
		Statement statemenet = null;
		boolean isTableCreated = false;
		try {
			connection.setAutoCommit(false);
			statemenet = connection.createStatement();
			for (String query : queries) {
				statemenet.addBatch(query);
			}
			statemenet.executeBatch();
			isTableCreated = true;

		} catch (Exception ex) {
			connection.rollback();
			isTableCreated = false;
			System.err.println("RolledBack:\n" + ex);
		} finally {
			try {
				statemenet.close();
			} catch (SQLException e1) {
				System.err.println("Statement Close Exception:" + e1);
				isTableCreated = false;
			}
			try {
				connection.setAutoCommit(true);
				connection.close();
			} catch (SQLException e) {
				System.err.println("Connection Close Exception:" + e);
				isTableCreated = false;
			}
		}
		return isTableCreated;
	}

	public Connection getConnection() throws ClassNotFoundException,
			SQLException {
		String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Local Drive (E)\\Tuhin Eclipse Workplace\\CognizantBankingSystem\\WebContent\\db\\MyDataBase.accdb;";
		// To work with Sourish Lapi
		String hostname = "Unknown";

		try {
			InetAddress addr;
			addr = InetAddress.getLocalHost();
			hostname = addr.getHostName();
			if ("Sourish-Lenovo".equalsIgnoreCase(hostname)) {
				database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\\Eclipse Workplace for Java\\CognizantBankingSystem\\WebContent\\db\\MyDataBase.accdb;";
			}
		} catch (UnknownHostException ex) {
			System.out.println("Hostname can not be resolved");
		}

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection connection = DriverManager.getConnection(database);
		return connection;
	}
}